package com.zyd.shiro.utils;

import com.zyd.shiro.business.annotation.FieldValue;
import com.zyd.shiro.persistence.mapper.PakAssetsClassMapper;
import com.zyd.shiro.persistence.mapper.SysDeptMapper;
import com.zyd.shiro.persistence.mapper.SysOrgMapper;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@SuppressWarnings("hiding")
public class ExcelTool {

    private static final int MAIN_ROW = 11;
    private static final int FROM_ROW = 14;

    public static final SimpleDateFormat simFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");




    @SneakyThrows
    private static Sheet createSheet(File file){
        Workbook workbook;
        if (file.getName().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        } else {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        }
        Sheet sheet =workbook.getSheetAt(0);
        return sheet;
    }

    public static List<Map<String, String>> listMainData(File file, Class item){
        return listData(createSheet(file),item,MAIN_ROW,MAIN_ROW+1);
    }
    public static List<Map<String, String>> listFromData(File file, Class item){
        Sheet sheet = createSheet(file);
        return listData(sheet,item,FROM_ROW,sheet.getPhysicalNumberOfRows());
    }
    public static List<Map<String, String>> listData(File file, Class item){
        Sheet sheet = createSheet(file);
        return listData(sheet,item,MAIN_ROW,sheet.getPhysicalNumberOfRows());
    }
    private static List<Map<String, String>> listData(Sheet sheet, Class clazz,int begin,int rows) {
        return parseExcelSheet(sheet,clazz,begin,rows);
    }




    /**
     * 解析Excel第一行，并生成以第一行表头为key，每一行的值为value的map
     * 例如: excel内容如下
     * 那么List<Map>就是的内容就是
     * List: map1( id: 1, alarm: abcd1 ),map2(( id: 2, alarm: abcd2 ).....
     */
    public static List<Map<String, String>> parseExcelSheet(Sheet sheet, Class cls,int start, int rows) {
        List<Map<String, String>> result = new ArrayList<>();
        Map<String, String> rowValue = null;
        int header = start - 1;
        String[] headers = getHeaderValue(sheet.getRow(header), cls);
        for (int i = start; i < rows; i++) {
            rowValue = new HashMap<>();
            Row row = sheet.getRow(i);
            for (int kk = 0; kk < headers.length; kk++) {
                System.out.println(headers[kk]+"-"+row.getCell(kk));
                if(null==row.getCell(kk)){
                    continue;
                }
                rowValue.put(headers[kk], String.valueOf(getCellValue(row.getCell(kk))));
            }
            result.add(rowValue);
        }
        return result;
    }

    /**
     * 获取第一行，表头，也就是实体类的字段，支持中英文，及下划线，忽略大小写，但是绝笔不能重复，表头有重复字段则不能解析
     **/
    private static String[] getHeaderValue(Row rowHeader, Class cls) {
        int colNum = rowHeader.getPhysicalNumberOfCells();
        String[] headValue = new String[rowHeader.getPhysicalNumberOfCells()];
        Map<String, String> map = getFieldAnnotation(cls);
        for (int i = 0; i < colNum; i++) {
            String title = rowHeader.getCell(i).getStringCellValue();
            headValue[i] = map.get(title);
        }
        return headValue;
    }

    public static Map<String, String> getFieldAnnotation(Class cls) {
        Map<String, String> map = new HashMap<>();
        //当父类为null的时候说明到达了最上层的父类(Object类).
        Field[] fields = getAllFields(cls);
        for (Field field : fields) {
            if (!field.getName().equals("serialVersionUID")) {
                FieldValue fieldValue = field.getAnnotation(FieldValue.class);
                if (fieldValue == null) {
                    continue;
                }
                map.put(fieldValue.name(), field.getName());
            }
        }
        return map;
    }

    @SuppressWarnings("deprecation")
    public static Object getCellValue(Cell cell) {
        Object value = null;
        switch (cell.getCellType().getCode()) {
            case 0:// 数字
                // 如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 注：format格式 yyyy-MM-dd hh:mm:ss
                    // 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    value = dateFomtConv(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    break;
                } else {
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case 1: // 字符串
                value = cell.getStringCellValue();
                break;
            case 4: // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case 2: // 公式
                value = cell.getCellFormula() + "";
                break;
            case 3: // 空值
                value = "";
                break;
            case 5: // 故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

    public static String dateFomtConv(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, 1);
        return simFormat.format(calendar.getTime());

    }


    public static  Field[] getAllFields(Class clazz){
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null){
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }


}